# -*- coding=utf8 -*-


import pymysql
import contextlib


@contextlib.contextmanager
def mysql(host='127.0.0.1', port=3306, user='community', passwd='www.jeenms.com', db='pospal', charset='utf8'):
    """
    定义上下文管理器，连接后自动关闭连接
    with mysql() as cursor:
        print(cursor)
        row_count = cursor.execute("select * from tb7")
    """
    # conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root', db='pospal', charset='utf8')
    conn = pymysql.connect(host=host, port=port, user=user, passwd=passwd, db=db, charset=charset)
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) #字典类型游标，可以使用result['id'], 默认游标取法result[1]
    conn.autocommit(0)
    try:
        yield cursor
    finally:
        conn.commit()
        cursor.close()
        conn.close()


# def get_system_param(item_name):
#     """
#     获取参数值
#     """
#     with mysql() as cursor:
#         select_sql = 'select item_value from sys_param where item_name=%s'
#         cursor.execute(select_sql, item_name)
#         result = cursor.fetchone()
#         return result['item_value']


# def update_system_param(item_name, item_value):
#     """
#     更新参数值
#     """
#     with mysql() as cursor:
#         update_sql = 'update sys_param set item_value=%s  where item_name=%s'
#         # param应该为tuple或者list
#         params = (item_value, item_name)
#         cursor.execute(update_sql, params)
